package com.cxm.report.dao.provider;

import com.cxm.report.model.IReport;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.util.StringUtils;

/**
 * @author scl.BlueOcean
 * @version 1.0
 * @date 2018年07月04 14:02
 * @description
 */
public class BrandReportProvider {
    private final String TBL_DAY_REPORT = "t_day_report";
    private final String TBL_BRAND = "brand_v1";
    private final String TBL_ORDER = "order_v1";
    private final String TBL_ORDER_RESULT = "order_result_v1";

    public String getBrandInfo(String date){
        StringBuffer sql = new StringBuffer("insert into ");
        sql.append(TBL_DAY_REPORT).append("(orderNum,brand_id,report_date)")
                .append("select count(1),brand_id,date(create_date) cday from ")
                .append(TBL_ORDER);

        if (StringUtils.hasText(date)) {
            sql.append(" where to_days(create_date)=to_days(#{date})");
        }
        sql.append(" group by brand_id,cday");
        return sql.toString();
    }
    public String delete(String date){
        SQL sql = new SQL().DELETE_FROM(TBL_DAY_REPORT);
        if (StringUtils.hasText(date)) {
            sql.WHERE("to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }
    public String updateBrandName(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(TBL_BRAND).append(" b set r.brandName = b.brand_name")
                .append(" where r.brand_id = b.brand_id");
        if (StringUtils.hasText(date)) {
            sql.append(" and to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }
    public String updateSuccessNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(" (select count(1) snum,  o.brand_id,avg(TIMESTAMPDIFF(SECOND,o.create_date,r.create_date)) qt,date(o.create_date) cday  from ")
                .append(TBL_ORDER).append(" o ")
                .append("join ").append(TBL_ORDER_RESULT).append(" r ")
                .append("on r.order_no=o.order_no and r.result_code=")
                .append(IReport.SUCCESS)
                .append(" group by o.brand_id,cday) s")
                .append(" set r.successNum=s.snum,r.avgQueryTime=s.qt ")
                .append(" where r.brand_id = s.brand_id and r.report_date=s.cday");
        if (StringUtils.hasText(date)) {
            sql.append(" and to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }
    /**
     * 更新查询失败数
     * */
    public String updateFailedNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(" (select count(1) snum,  o.brand_id,date(o.create_date) cday  from ")
                .append(TBL_ORDER).append(" o ")
                .append("join ").append(TBL_ORDER_RESULT).append(" r ")
                .append("on r.order_no=o.order_no and r.result_code in (")
                .append(IReport.FAILED).append(",").append(IReport.WEI_HU).append(")")
                .append(" group by o.brand_id,cday) s")
                .append(" set r.failedNum=s.snum ")
                .append(" where r.brand_id = s.brand_id and r.report_date=s.cday");
        if (StringUtils.hasText(date)) {
            sql.append(" and to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }
    /**
     * 更新查询超时数
     * @return
     */
    public String updateOverTimeNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(" (select count(1) snum,  o.brand_id,date(o.create_date) cday  from ")
                .append(TBL_ORDER).append(" o ")
                .append("join ").append(TBL_ORDER_RESULT).append(" r ")
                .append("on r.order_no=o.order_no and r.result_code in (")
                .append(IReport.OVER_TIME).append(",").append(IReport.OVER_TIME_GIVE_UP).append(")")
                .append(" group by o.brand_id,cday) s")
                .append(" set r.overTimeNum=s.snum ")
                .append(" where r.brand_id = s.brand_id and r.report_date=s.cday");
        if (StringUtils.hasText(date)) {
            sql.append(" and to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }

    /**
     * 更新查无记录sql
     * @param date
     * @return
     */
    public String updateNoRecordNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(" (select count(1) snum,  o.brand_id,date(o.create_date) cday  from ")
                .append(TBL_ORDER).append(" o ")
                .append("join ").append(TBL_ORDER_RESULT).append(" r ")
                .append("on r.order_no=o.order_no and r.result_code=")
                .append(IReport.NO_RECORD)
                .append(" group by o.brand_id,cday) s")
                .append(" set r.noRecordNum=s.snum ")
                .append(" where r.brand_id = s.brand_id and r.report_date=s.cday");
        if (StringUtils.hasText(date)) {
            sql.append(" and to_days(report_date)=to_days(#{date})");
        }
        return sql.toString();
    }
    public String updateInquiryNum(String date){
        StringBuffer sql = new StringBuffer("update ");
        sql.append(TBL_DAY_REPORT).append(" r,")
                .append(" (select count(1) snum, brand_id,date(create_date) cday  from ")
                .append(TBL_ORDER)
                .append(" where order_no not in(select DISTINCT order_no from order_result_v1) ");
                if (StringUtils.hasText(date)) {
                    sql.append(" and to_days(create_date)=to_days(#{date})");
                }
        sql .append(" group by brand_id,cday) s set r.inquiryNum=s.snum")
            .append(" where r.brand_id = s.brand_id and r.report_date=s.cday");
        return sql.toString();
    }
}
